/*
 * @(#)FormDataElement.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.form.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import com.dcivision.form.bean.FormData;
import com.dcivision.form.bean.FormDataElement;
import com.dcivision.form.bean.FormElement;
import com.dcivision.form.bean.FormElementType;
import com.dcivision.form.bean.FormFileAttachment;
import com.dcivision.form.bean.FormSubmission;
import com.dcivision.form.web.ListFormSubmissionForm;
import com.dcivision.form.web.MaintFormSubmissionForm;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.dao.Query;
import com.dcivision.framework.dao.QueryUtil;
import com.dcivision.framework.web.AbstractActionForm;
import com.dcivision.framework.web.AbstractSearchForm;

/**
  FormDataElementDAObject.java

  This class is the data access bean for table "FORM_DATA_ELEMENT".

  @author      Scott Tong
  @company     DCIVision Limited
  @creation date   07/01/2004
  @version     $Revision: 1.65 $
*/

public class FormDataElementDAObject extends AbstractDAObject {

  public static final String REVISION = "$Revision: 1.65 $";

  public static final String TABLE_NAME = "FORM_DATA_ELEMENT";

  public FormDataElementDAObject(SessionContainer sessionContainer, Connection dbConn) {
    super(sessionContainer, dbConn);
  }

  protected void initDBSetting() {
    this.baseTableName = TABLE_NAME;
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add("PAGE_ID");
    this.vecDBColumn.add("SECTION_ID");
    this.vecDBColumn.add("SUBMISSION_ID");
    this.vecDBColumn.add("ELEMENT_ID");
    this.vecDBColumn.add("WORKFLOW_PROGRESS_ID");
    this.vecDBColumn.add("SEQ");
    // this.vecDBColumn.add("SECTION_SEQ");
    // this.vecDBColumn.add("ELEMENT_SEQ");
    this.vecDBColumn.add("RECORD_STATUS");
    this.vecDBColumn.add("UPDATE_COUNT");
    this.vecDBColumn.add("CREATOR_ID");
    this.vecDBColumn.add("CREATE_DATE");
    this.vecDBColumn.add("UPDATER_ID");
    this.vecDBColumn.add("UPDATE_DATE");
  }

  protected synchronized AbstractBaseObject getByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE  A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          // tmpFormDataElement.setSectionSeq(getResultSetInteger(rs, "SECTION_SEQ"));
          // tmpFormDataElement.setElementSeq(getResultSetInteger(rs, "ELEMENT_SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
          return(tmpFormDataElement);
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");

        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 2, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 2, searchKeyword);
        }
        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt<pageSize) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
          tmpFormDataElement.setRecordCount(totalNumOfRecord);
          tmpFormDataElement.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpFormDataElement);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }

  protected synchronized List getList() throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
          result.add(tmpFormDataElement);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {
  }

  protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {
  }

  protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {
  }

  protected synchronized AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    FormDataElement tmpFormDataElement = (FormDataElement)((FormDataElement)obj).clone();

    synchronized(dbConn) {
      try {
        Integer nextID = getNextPrimaryID();
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("INSERT ");
        sqlStat.append("INTO   FORM_DATA_ELEMENT(ID, PAGE_ID, SECTION_ID, SUBMISSION_ID, ELEMENT_ID, WORKFLOW_PROGRESS_ID, SEQ, SUBMIT_FLAG, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) ");
        sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, tmpFormDataElement.getPageID());
        setPrepareStatement(preStat, 3, tmpFormDataElement.getSectionID());
        setPrepareStatement(preStat, 4, tmpFormDataElement.getSubmissionID());
        setPrepareStatement(preStat, 5, tmpFormDataElement.getElementID());
        setPrepareStatement(preStat, 6, tmpFormDataElement.getWorkflowProgressID());
        setPrepareStatement(preStat, 7, tmpFormDataElement.getSeq());
        // setPrepareStatement(preStat, 8, tmpFormDataElement.getSectionSeq());
        // setPrepareStatement(preStat, 9, tmpFormDataElement.getElementSeq());
        setPrepareStatement(preStat, 8, tmpFormDataElement.getSubmitFlag());
        setPrepareStatement(preStat, 9, GlobalConstant.RECORD_STATUS_ACTIVE);
        setPrepareStatement(preStat, 10, new Integer(0));
        setPrepareStatement(preStat, 11, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 12, currTime);
        setPrepareStatement(preStat, 13, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 14, currTime);
        preStat.executeUpdate();
        tmpFormDataElement.setID(nextID);
        tmpFormDataElement.setCreatorID(sessionContainer.getUserRecordID());
        tmpFormDataElement.setCreateDate(currTime);
        tmpFormDataElement.setUpdaterID(sessionContainer.getUserRecordID());
        tmpFormDataElement.setUpdateDate(currTime);
        tmpFormDataElement.setUpdateCount(new Integer(0));
        tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
        tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
        return(tmpFormDataElement);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }


    protected synchronized AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException {
      PreparedStatement preStat = null;
      StringBuffer sqlStat = new StringBuffer();
      FormDataElement tmpFormDataElement = (FormDataElement)((FormDataElement)obj).clone();

      synchronized(dbConn) {
        try {
          int updateCnt = 0;
          Timestamp currTime = Utility.getCurrentTimestamp();
          sqlStat.append("UPDATE FORM_DATA_ELEMENT ");
          sqlStat.append("SET  PAGE_ID=?, SECTION_ID=?, SUBMISSION_ID=?, ELEMENT_ID=?, WORKFLOW_PROGRESS_ID=?, SEQ=?, SUBMIT_FLAG=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=? ");
          sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          setPrepareStatement(preStat, 1, tmpFormDataElement.getPageID());
          setPrepareStatement(preStat, 2, tmpFormDataElement.getSectionID());
          setPrepareStatement(preStat, 3, tmpFormDataElement.getSubmissionID());
          setPrepareStatement(preStat, 4, tmpFormDataElement.getElementID());
          setPrepareStatement(preStat, 5, tmpFormDataElement.getWorkflowProgressID());
          setPrepareStatement(preStat, 6, tmpFormDataElement.getSeq());
          setPrepareStatement(preStat, 7, tmpFormDataElement.getSubmitFlag());
          setPrepareStatement(preStat, 8, new Integer(tmpFormDataElement.getUpdateCount().intValue() + 1));
          setPrepareStatement(preStat, 9, sessionContainer.getUserRecordID());
          setPrepareStatement(preStat, 10, currTime);
          setPrepareStatement(preStat, 11, tmpFormDataElement.getID());
          setPrepareStatement(preStat, 12, tmpFormDataElement.getUpdateCount());
          updateCnt = preStat.executeUpdate();
          if (updateCnt == 0) {
            throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
          } else {
            tmpFormDataElement.setUpdaterID(sessionContainer.getUserRecordID());
            tmpFormDataElement.setUpdateDate(currTime);
            tmpFormDataElement.setUpdateCount(new Integer(tmpFormDataElement.getUpdateCount().intValue() + 1));
            tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
            tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
            return(tmpFormDataElement);
          }
        } catch (ApplicationException appEx) {
          throw appEx;
        } catch (SQLException sqle) {
          log.error(sqle, sqle);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
        } finally {
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }
    }



  public synchronized  void updateByFile(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    FormDataElement tmpFormDataElement = (FormDataElement)((FormDataElement)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE FORM_DATA_ELEMENT ");
        sqlStat.append("SET  PAGE_ID=?, SECTION_ID=?, SUBMISSION_ID=?, ELEMENT_ID=?,UPDATE_COUNT=UPDATE_COUNT+1 ");
        sqlStat.append("WHERE  ID=?  ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpFormDataElement.getPageID());
        setPrepareStatement(preStat, 2, tmpFormDataElement.getSectionID());
        setPrepareStatement(preStat, 3, tmpFormDataElement.getSubmissionID());
        setPrepareStatement(preStat, 4, tmpFormDataElement.getElementID());
        setPrepareStatement(preStat, 5, tmpFormDataElement.getSeq());
        setPrepareStatement(preStat, 6, new Integer(tmpFormDataElement.getUpdateCount().intValue() + 1));
        setPrepareStatement(preStat, 7, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 8, currTime);
        setPrepareStatement(preStat, 9, tmpFormDataElement.getID());
        setPrepareStatement(preStat, 10, tmpFormDataElement.getUpdateCount());
        preStat.executeUpdate();
        dbConn.commit();
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    FormDataElement tmpFormDataElement = (FormDataElement)((FormDataElement)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpFormDataElement.getID());
        setPrepareStatement(preStat, 2, tmpFormDataElement.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return(tmpFormDataElement);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException {
    Vector oldValues = new Vector();
    Vector newValues = new Vector();
    FormDataElement tmpFormDataElement = (FormDataElement)this.oldValue;

    if (tmpFormDataElement != null) {
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getID()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getPageID()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getSectionID()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getSubmissionID()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getElementID()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getWorkflowProgressID()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getSeq()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getSubmitFlag()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpFormDataElement.getUpdateDate()));
    }

    tmpFormDataElement = (FormDataElement)obj;
    if (tmpFormDataElement != null) {
      newValues.add(toAuditTrailValue(tmpFormDataElement.getID()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getPageID()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getSectionID()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getSubmissionID()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getElementID()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getWorkflowProgressID()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getSeq()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getSubmitFlag()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getRecordStatus()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getUpdateCount()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getCreatorID()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getCreateDate()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getUpdaterID()));
      newValues.add(toAuditTrailValue(tmpFormDataElement.getUpdateDate()));
    }

    auditTrailBase(opMode, oldValues, newValues);
  }

  /***********************************************************************
   * DON'T Modify the codes above unless you know what you are doing!!!  *
   * Put your own functions beblow.                                      *
   * For FINDER methods, the function name should be in the notation:    *
   *   public Object getObjectBy<Criteria>()                             *
   *   - e.g. public Object getObjectByCode()                            *
   *   public List getListBy<Criteria>()                                 *
   *   - e.g. public List getListByUserID()                              *
   * For OPERATION methods, the function name should be in the notation: *
   *   public void <Operation>ObjectBy<Criteria>()                       *
   *   - e.g. public void deleteObjectByCode()                           *
   *   public void <Operation>ListBy<Criteria>()                         *
   *   - e.g. public void deleteListByUserID()                           *
   ***********************************************************************/
   
  /**
   * Get the FormDataElement Object by Form Submission ID and Form ElementID
   * @param submissionID form submission ID
   * @param elementID form element ID
   * @return FormDataElement
   * @throws ApplicationException
   */
  public synchronized AbstractBaseObject getBySubmissionIDElementID(   
      Integer submissionID,
      Integer elementID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE  A.SUBMISSION_ID =? AND A.ELEMENT_ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, submissionID);
        this.setPrepareStatement(preStat, 2, elementID);
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
          return(tmpFormDataElement);
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * Get the FormDataElement by FormPageID,FormSectionID,FormSubmissionID and Form ElementID
   * @param pageID form page ID
   * @param sectionID form section ID
   * @param submissionID form submission ID
   * @param elementID form element ID
   * @return FormDataElement
   * @throws ApplicationException
   */
  public synchronized AbstractBaseObject getByPageIDSectionIDSubmissionIDElementID(
      Integer pageID,
      Integer sectionID,
      Integer submissionID,
      Integer elementID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE  A.PAGE_ID = ? AND A.SECTION_ID = ? AND A.SUBMISSION_ID =? AND A.ELEMENT_ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, pageID);
        this.setPrepareStatement(preStat, 2, sectionID);
        this.setPrepareStatement(preStat, 3, submissionID);
        this.setPrepareStatement(preStat, 4, elementID);
        this.setPrepareStatement(preStat, 5, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
          return(tmpFormDataElement);

        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * Get all values of the formDataElement by page ID,section ID and element ID
   * @param pageID page ID
   * @param sectionID section ID
   * @param elementID element ID
   * @return all values of the formDataElement
   * @throws ApplicationException
   */
  public synchronized String getAllValuesByPageIDSectionIDElementID(
      Integer pageID,
      Integer sectionID,
      Integer elementID,
      AbstractActionForm searchForm
    ) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStat2 = null;
    ResultSet rs = null;
    ResultSet rs2 = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStat2 = new StringBuffer();
    List result = new ArrayList();
    String valueString = "";
    String additiveQuery = "";

    MaintFormSubmissionForm maintFormSubmissionForm = (MaintFormSubmissionForm)searchForm;
    String fromDate = maintFormSubmissionForm.getFromDate();
    String toDate = TextUtility.dbDateAdder(maintFormSubmissionForm.getToDate(), 1);
    String basicSearchKeyword = maintFormSubmissionForm.getBasicSearchKeyword();
    String searchStatus = maintFormSubmissionForm.getSearchStatus();
    String searchSubmitter = maintFormSubmissionForm.getSearchSubmitter();
    FormFileAttachmentDAObject formFileAttachDAO = new FormFileAttachmentDAObject(sessionContainer,dbConn);
    
    if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
      if (!Utility.isEmpty(fromDate) && Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE >= TO_DATE('" + fromDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
      } else if (Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE <= TO_DATE('" + toDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
      } else if (!Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE BETWEEN TO_DATE('" + fromDate + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('"+  toDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
      }
    } else {
      if (!Utility.isEmpty(fromDate) && Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE >= '" + fromDate + "' ";
      } else if (Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE <= '" + toDate + "' ";
      } else if (!Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE BETWEEN '" + fromDate + "' AND '"+  toDate + "' ";
      }
    }
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, F.ELEMENT_TYPE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A, FORM_SUBMISSION B, FORM_DATA C, FORM_ELEMENT F ");
        sqlStat.append("WHERE  A.SUBMISSION_ID = B.ID AND C.DATA_ELEMENT_ID = A.ID AND A.ELEMENT_ID = F.ID ");
        sqlStat.append("AND B.RECORD_STATUS = A.RECORD_STATUS AND C.RECORD_STATUS = A.RECORD_STATUS AND A.RECORD_STATUS = F.RECORD_STATUS ");
        sqlStat.append("AND A.PAGE_ID = ? AND A.SECTION_ID = ? AND A.ELEMENT_ID = ? AND A.RECORD_STATUS = ? ");
        
        if (!Utility.isEmpty(searchStatus)) {
          sqlStat.append("AND B.STATUS = '" + searchStatus + "' ");
        }else{
          sqlStat.append("AND B.STATUS <> '" + FormSubmission.FORM_SUBMISSION_STATU_SAVED + "' ");
        }
        if (!Utility.isEmpty(searchSubmitter)) {
          sqlStat.append("AND B.CREATOR_ID = "+ searchSubmitter +" ");
        }
        //search submissionID
        if(!Utility.isEmpty(basicSearchKeyword)){
          // For Text Box
            sqlStat2.append("SELECT DISTINCT E.SUBMISSION_ID FROM FORM_DATA_ELEMENT E, FORM_DATA D ");
          sqlStat2.append("WHERE D.DATA_ELEMENT_ID = E.ID ");
          String searchField = maintFormSubmissionForm.getBasicSearchField();
          String searchKeyword = this.getFormattedKeyword(maintFormSubmissionForm.getBasicSearchKeyword(), maintFormSubmissionForm.getBasicSearchType());
          sqlStat2.append("AND D.DATA_STRING " + maintFormSubmissionForm.getBasicSearchType() + " ? ");
          if(!Utility.isEmpty(basicSearchKeyword) && !Utility.isEmpty(searchField)){
            sqlStat2.append("AND E.ELEMENT_ID = ? ");
          }
          preStat2 = dbConn.prepareStatement(sqlStat2.toString());
          this.setPrepareStatement(preStat2, 1, searchKeyword);  
          if(!Utility.isEmpty(basicSearchKeyword) && !Utility.isEmpty(searchField)){
            this.setPrepareStatement(preStat2, 2, searchField);
          }
          rs2= preStat2.executeQuery();
          String submissionID = "";
          while (rs2.next()) {
            submissionID += ((submissionID.length()>0)?",":"") + getResultSetInteger(rs2, "SUBMISSION_ID");
          }
          // For Select Box, Radio Button, Check Box
          sqlStat2 = new StringBuffer();
          sqlStat2.append("SELECT DISTINCT E.SUBMISSION_ID ");
          sqlStat2.append("FROM FORM_DATA_ELEMENT E, FORM_DATA D, FORM_SELECTION_FIELD F, FORM_SELECTION_FIELD_OPTION G ");
          sqlStat2.append("WHERE D.DATA_ELEMENT_ID = E.ID AND G.SELECTION_FIELD_ID = F.ID AND E.ELEMENT_ID = F.ELEMENT_ID AND D.DATA_STRING = G.START_VALUE ");
          sqlStat2.append("AND G.LABEL " + maintFormSubmissionForm.getBasicSearchType() + " ? ");
          if(!Utility.isEmpty(basicSearchKeyword) && !Utility.isEmpty(searchField)){
            sqlStat2.append("AND E.ELEMENT_ID = ? ");
          }
          preStat2 = dbConn.prepareStatement(sqlStat2.toString());
          this.setPrepareStatement(preStat2, 1, searchKeyword);  
          if(!Utility.isEmpty(basicSearchKeyword) && !Utility.isEmpty(searchField)){
            this.setPrepareStatement(preStat2, 2, searchField);
          }
          rs2= preStat2.executeQuery();
          while (rs2.next()) {
            submissionID += ((submissionID.length()>0)?",":"") + getResultSetInteger(rs2, "SUBMISSION_ID");
          }

          if (!Utility.isEmpty(submissionID)) {
            sqlStat.append("           AND B.ID IN ("+submissionID+") ");
          }else{
            throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
          }
          //}
          try { rs2.close(); } catch (Exception ignore) {} finally { rs2 = null; }
          try { preStat2.close(); } catch (Exception ignore) {} finally { preStat2 = null; }
        }

        sqlStat.append(additiveQuery);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, pageID);
        this.setPrepareStatement(preStat, 2, sectionID);
        this.setPrepareStatement(preStat, 3, elementID);
        this.setPrepareStatement(preStat, 4, GlobalConstant.RECORD_STATUS_ACTIVE);

        //this.setPrepareStatement(preStat, 6, sessionContainer.getUserRecordID());

        rs = preStat.executeQuery();
        boolean isFirstItem = true;
        while (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs,"ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));

          //get the value of the element
          FormDataDAObject formDataDAO = new FormDataDAObject(sessionContainer,dbConn);
          FormData formData = (FormData)formDataDAO.getByDataElementID(tmpFormDataElement.getID());
          List tmpAttachmentList = new ArrayList();
          //if element type is file box, then get name from FORM_FILE_ATTACHMENT table.
          if (FormElementType.ELEMENT_FILE.equals(getResultSetString(rs, "ELEMENT_TYPE"))){
            tmpAttachmentList = formFileAttachDAO.getFormFileAttachmentListByFormDataElementID(tmpFormDataElement.getID());
            if (!Utility.isEmpty(tmpAttachmentList)){
              for (int i=0; i<tmpAttachmentList.size(); i++) {
                FormFileAttachment attachment = (FormFileAttachment)tmpAttachmentList.get(i);  
                valueString += (isFirstItem)?"":"<font color='red'>, </font>";
                if (Utility.isEmpty(attachment.getAttachmentName()) ) {
                  isFirstItem = true;
                } else {
                  valueString += attachment.getAttachmentName();
                  isFirstItem = false;
                }
              }
            }
          }else{
            //get value from FORM_DATA table.
            valueString += (isFirstItem)?"":"<font color='red'>, </font>";
            if (Utility.isEmpty(formData.getDataString()) ) {
              isFirstItem = true;
            } else {
              valueString += formData.getDataString();
              isFirstItem = false;
            }
          }
        }
        return valueString;
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * Get the FormData list by the PageID,SectionID,SubmissionID and ElementID
   * @param pageID
   * @param sectionID
   * @param submissionID
   * @param elementID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getElementValueByPageIDSectionIDSubmissionIDElementID(
      Integer pageID,
      Integer sectionID,
      Integer submissionID,
      Integer elementID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT B.ID, B.DATA_ELEMENT_ID, B.DATA_STRING, B.DATA_NUMERIC, B.DATA_DATETIME, B.RECORD_STATUS, B.UPDATE_COUNT, B.CREATOR_ID, B.CREATE_DATE, B.UPDATER_ID, B.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A, FORM_DATA B ");
        sqlStat.append("WHERE A.ID = B.DATA_ELEMENT_ID AND A.RECORD_STATUS = B.RECORD_STATUS ");
        sqlStat.append("AND A.PAGE_ID = ? AND A.SECTION_ID = ? AND A.SUBMISSION_ID =? AND A.ELEMENT_ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, pageID);
        this.setPrepareStatement(preStat, 2, sectionID);
        this.setPrepareStatement(preStat, 3, submissionID);
        this.setPrepareStatement(preStat, 4, elementID);
        this.setPrepareStatement(preStat, 5, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();

        while (rs.next()) {
          FormData tmpFormData = new FormData();
          tmpFormData.setID(getResultSetInteger(rs, "ID"));
          tmpFormData.setDataElementID(getResultSetInteger(rs, "DATA_ELEMENT_ID"));
          tmpFormData.setDataString(getResultSetString(rs, "DATA_STRING"));
          tmpFormData.setDataNumeric(getResultSetFloat(rs, "DATA_NUMERIC"));
          tmpFormData.setDataDatetime(getResultSetTimestamp(rs, "DATA_DATETIME"));
          tmpFormData.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormData.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormData.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormData.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormData.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormData.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormData.setCreatorName(UserInfoFactory.getUserFullName(tmpFormData.getCreatorID()));
          tmpFormData.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormData.getUpdaterID()));
          result.add(tmpFormData);

        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Get the FormData list by the PageID,SectionID,SubmissionID and ElementID
   * @param pageID
   * @param sectionID
   * @param submissionID
   * @param elementID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getElementValueByPageIDSectionIDSectionSeqSubmissionIDElementIDElementSeq(
      Integer pageID,
      Integer sectionID,
      Integer submissionID,
      Integer elementID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT B.ID, B.DATA_ELEMENT_ID, B.DATA_STRING, B.DATA_NUMERIC, B.DATA_DATETIME, B.RECORD_STATUS, B.UPDATE_COUNT, B.CREATOR_ID, B.CREATE_DATE, B.UPDATER_ID, B.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A, FORM_DATA B ");
        sqlStat.append("WHERE A.ID = B.DATA_ELEMENT_ID AND A.RECORD_STATUS = B.RECORD_STATUS ");
        sqlStat.append("AND A.PAGE_ID = ? AND A.SECTION_ID = ? AND A.SUBMISSION_ID =? AND A.ELEMENT_ID = ? AND A.RECORD_STATUS = ? ");

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, pageID);
        this.setPrepareStatement(preStat, 2, sectionID);
        // this.setPrepareStatement(preStat, 3, sectionSeq);
        this.setPrepareStatement(preStat, 3, submissionID);
        this.setPrepareStatement(preStat, 4, elementID);
        // this.setPrepareStatement(preStat, 6, elementSeq);
        this.setPrepareStatement(preStat, 5, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();

        while (rs.next()) {
          FormData tmpFormData = new FormData();
          tmpFormData.setID(getResultSetInteger(rs, "ID"));
          tmpFormData.setDataElementID(getResultSetInteger(rs, "DATA_ELEMENT_ID"));
          tmpFormData.setDataString(getResultSetString(rs, "DATA_STRING"));
          tmpFormData.setDataNumeric(getResultSetFloat(rs, "DATA_NUMERIC"));
          tmpFormData.setDataDatetime(getResultSetTimestamp(rs, "DATA_DATETIME"));
          tmpFormData.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormData.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormData.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormData.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormData.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormData.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormData.setCreatorName(UserInfoFactory.getUserFullName(tmpFormData.getCreatorID()));
          tmpFormData.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormData.getUpdaterID()));
          result.add(tmpFormData);

        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * Get the latest seq (whether it's status is inactive or anyone is the creator or it's submit_flag
   * is pick up or submit etc) of one progress by given parameters.
   * 
   * @param pageID
   * @param sectionID
   * @param sectionSeq
   * @param submissionID
   * @param elementID
   * @param elementSeq
   * @param progressID
   * @return the value of latest seq, return null if none of record is found.
   * @throws ApplicationException
   */
  public synchronized Long getLatestSeqByPageIDSectionIDSubmissionIDElementIDProgressID(
      Integer pageID,
      Integer sectionID,
      Integer submissionID,
      Integer elementID,
      Integer progressID
      ) throws ApplicationException {
    
    Long latestSeq = null;
    
    PreparedStatement seqState = null;
    ResultSet seqRS = null;
    
    Query seqQuery = QueryUtil.createQuery();
    int seqQueryIndex = 1;
    
    seqQuery.append(
        "SELECT MAX(SEQ) \n" +
        "FROM \n" +
        " FORM_DATA_ELEMENT \n" +
        "WHERE \n" +
        " PAGE_ID = ? AND SECTION_ID = ? " +
        " AND SUBMISSION_ID = ? AND ELEMENT_ID = ? " +
        " AND WORKFLOW_PROGRESS_ID = ? \n" // Filter by given parameters.
        );
    
    seqQuery.setParameter(seqQueryIndex++, pageID);
    seqQuery.setParameter(seqQueryIndex++, sectionID);
    seqQuery.setParameter(seqQueryIndex++, submissionID);
    seqQuery.setParameter(seqQueryIndex++, elementID);
    seqQuery.setParameter(seqQueryIndex++, progressID);
    
    synchronized(dbConn) {
      try {
        seqState = dbConn.prepareStatement(seqQuery.toString());
        this.setPreparedStatement(seqState, 1, seqQuery.getAllParameters());
        seqRS = seqState.executeQuery();
        
        while (!Utility.isEmpty(seqRS) && seqRS.next()) {
          Object value = seqRS.getObject(1);
          
          if (value == null || !(value instanceof Number)) { // Break if value is null or value isnt a number.
            break;
          }
          
          latestSeq = new Long(((Number) value).longValue());
        }
        
        return latestSeq;
        
      } catch (SQLException sqlE) {
        log.error(sqlE, sqlE);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqlE, sqlE.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          seqRS.close();
          seqState.close();
        } catch (Exception ignore) {
        } finally {
          seqRS = null;
          seqState = null;
        }
      }
    }
  }
  
  /**
   * Get the latest record (whether it's status is inactive or anyone is the creator or it's submit_flag
   * is pick up or submit etc) of one progress by given parameters.
   * 
   * @param pageID
   * @param sectionID
   * @param sectionSeq
   * @param submissionID
   * @param elementID
   * @param elementSeq
   * @param progressID
   * @return the latest record, an empty list will return if none of suitable record found.
   * @throws ApplicationException
   */
  public synchronized List getElementValueByPageIDSectionIDSubmissionIDElementIDProgressID(
	      Integer pageID,
	      Integer sectionID,
	      Integer submissionID,
	      Integer elementID,
	      Integer progressID)
	      throws ApplicationException {
    
    List result = new ArrayList();

    // Phase 1: found out the latest seq.
    Long latestSeq = getLatestSeqByPageIDSectionIDSubmissionIDElementIDProgressID(
        pageID,
        sectionID,
        submissionID,
        elementID,
        progressID
        );
    
    if (Utility.isEmpty(latestSeq)) {
      return result;
    }
    
    // Phase 2: found out the latest record by given parameters and the latest seq.
	    PreparedStatement preStat = null;
	    ResultSet rs = null;
	    StringBuffer sqlStat = new StringBuffer();
      
	    synchronized(dbConn) {
	      try {
	        sqlStat.append("SELECT B.ID, B.DATA_ELEMENT_ID, B.DATA_STRING, B.DATA_NUMERIC, B.DATA_DATETIME, B.RECORD_STATUS, B.UPDATE_COUNT, B.CREATOR_ID, B.CREATE_DATE, B.UPDATER_ID, B.UPDATE_DATE ");
	        sqlStat.append("FROM   FORM_DATA_ELEMENT A, FORM_DATA B ");
	        sqlStat.append("WHERE A.ID = B.DATA_ELEMENT_ID AND A.RECORD_STATUS = B.RECORD_STATUS ");
	        sqlStat.append("AND A.PAGE_ID = ? AND A.SECTION_ID = ? AND A.SUBMISSION_ID =? AND A.ELEMENT_ID = ? ");
	        sqlStat.append("AND A.WORKFLOW_PROGRESS_ID =?  ");
	        sqlStat.append("AND A.SEQ = ? ");
          
	        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        this.setPrepareStatement(preStat, 1, pageID);
	        this.setPrepareStatement(preStat, 2, sectionID);
	        this.setPrepareStatement(preStat, 3, submissionID);
	        this.setPrepareStatement(preStat, 4, elementID);
	        this.setPrepareStatement(preStat, 5, progressID);
	        this.setPrepareStatement(preStat, 6, latestSeq);
	        rs = preStat.executeQuery();

	        while (rs.next()) {
	          FormData tmpFormData = new FormData();
	          tmpFormData.setID(getResultSetInteger(rs, "ID"));
	          tmpFormData.setDataElementID(getResultSetInteger(rs, "DATA_ELEMENT_ID"));
	          tmpFormData.setDataString(getResultSetString(rs, "DATA_STRING"));
	          tmpFormData.setDataNumeric(getResultSetFloat(rs, "DATA_NUMERIC"));
	          tmpFormData.setDataDatetime(getResultSetTimestamp(rs, "DATA_DATETIME"));
	          tmpFormData.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
	          tmpFormData.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
	          tmpFormData.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
	          tmpFormData.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
	          tmpFormData.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
	          tmpFormData.setCreatorName(UserInfoFactory.getUserFullName(tmpFormData.getCreatorID()));
	          tmpFormData.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormData.getUpdaterID()));
	          result.add(tmpFormData);

	        }
	        return(result);
	      } catch (ApplicationException appEx) {
	        throw appEx;
	      } catch (SQLException sqle) {
	        log.error(sqle, sqle);
	        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
	      } catch (Exception e) {
	        log.error(e, e);
	        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
	      } finally {
	        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
	        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
	      }
	    }
	  }
  public synchronized int getCountByPageIDSectionIDSubmissionIDElementIDElement(
      Integer pageID,
      Integer sectionID,
      Integer submissionID,
      Integer elementID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    int result = 1;
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT COUNT(DISTINCT A.ELEMENT_SEQ) ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE ");
        sqlStat.append("A.PAGE_ID = ? AND A.SECTION_ID = ? AND A.SUBMISSION_ID =? AND A.ELEMENT_ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, pageID);
        this.setPrepareStatement(preStat, 2, sectionID);
        this.setPrepareStatement(preStat, 3, submissionID);
        this.setPrepareStatement(preStat, 4, elementID);
        this.setPrepareStatement(preStat, 5, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();

        if (rs.next()) {
          result = rs.getInt(1);
        }
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }


  public synchronized List getListByPageIDSubmissionID(
      Integer pageID,
      Integer submissionID
      ) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        sqlStat.append("AND  A.PAGE_ID = ? AND A.SUBMISSION_ID = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, pageID);
        this.setPrepareStatement(preStat, 3, submissionID);

        rs = preStat.executeQuery();
        while (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
          result.add(tmpFormDataElement);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized List getListBySubmissionID(
      Integer submissionID
      ) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        sqlStat.append("AND A.SUBMISSION_ID = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, submissionID);

        rs = preStat.executeQuery();
        while (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
          result.add(tmpFormDataElement);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  

  public synchronized List getListBySectionIDSubmissionID(
      Integer sectionID,
      Integer submissionID
      ) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        sqlStat.append("AND  A.SECTION_ID = ? AND A.SUBMISSION_ID = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, sectionID);
        this.setPrepareStatement(preStat, 3, submissionID);

        rs = preStat.executeQuery();
        while (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
          result.add(tmpFormDataElement);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
 
  
  public synchronized List getFormElementFormDataByPageIDSectionID(
      Integer pageID,
      Integer sectionID,
      AbstractActionForm searchForm
    ) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    PreparedStatement preStat1 = null;
    ResultSet rs1 = null;
    StringBuffer sqlStat1 = new StringBuffer();
    PreparedStatement preStat2 = null;
    ResultSet rs2 = null;
    StringBuffer sqlStat2 = new StringBuffer();
    String submissionID = "";
    List result = new ArrayList();  
    String additiveQuery = "";

    MaintFormSubmissionForm maintFormSubmissionForm = (MaintFormSubmissionForm)searchForm;
    String fromDate = maintFormSubmissionForm.getFromDate();
    String toDate = TextUtility.dbDateAdder(maintFormSubmissionForm.getToDate(), 1);
    String basicSearchKeyword = maintFormSubmissionForm.getBasicSearchKeyword();
    String searchStatus = maintFormSubmissionForm.getSearchStatus();
    String searchSubmitter = maintFormSubmissionForm.getSearchSubmitter();

    if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
      if (!Utility.isEmpty(fromDate) && Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE >= TO_DATE('" + fromDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
      } else if (Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE <= TO_DATE('" + toDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
      } else if (!Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE BETWEEN TO_DATE('" + fromDate + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('"+  toDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
      }
    } else {
      if (!Utility.isEmpty(fromDate) && Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE >= '" + fromDate + "' ";
      } else if (Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE <= '" + toDate + "' ";
      } else if (!Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        additiveQuery = " AND B.CREATE_DATE BETWEEN '" + fromDate + "' AND '"+  toDate + "' ";
      }
    }
  
    synchronized(dbConn) {
      try {

        sqlStat1.append("SELECT DISTINCT E.ID ");
        sqlStat1.append("FROM FORM_ELEMENT D, MTM_FORM_PAGE_FORM_SECTION MP, MTM_FORM_SECTION_FORM_ELEMENT MS, FORM_SUBMISSION E, FORM_RECORD F, FORM_PAGE P, FORM_DATA_ELEMENT G  ");
        sqlStat1.append("WHERE MP.SECTION_ID = MS.SECTION_ID AND D.ID = MS.ELEMENT_ID AND E.FORM_RECORD_ID = F.ID AND F.ID = P.FORM_RECORD_ID AND G.PAGE_ID = MP.PAGE_ID AND MS.SECTION_ID = G.SECTION_ID AND G.SUBMISSION_ID = E.ID ");
        sqlStat1.append("AND MP.PAGE_ID = '"+pageID+"' AND MS.SECTION_ID = '"+sectionID+"' ");
        if (!Utility.isEmpty(searchStatus)) {
          sqlStat1.append("AND E.STATUS = '" + searchStatus + "' ");
        }else{
          sqlStat1.append("AND E.STATUS <> '" + FormSubmission.FORM_SUBMISSION_STATU_SAVED + "' ");
        }
        if (!Utility.isEmpty(searchSubmitter)) {
          sqlStat1.append("AND E.CREATOR_ID = " + searchSubmitter + " ");
        }

        //search field value
        if(!Utility.isEmpty(basicSearchKeyword)){
          // For Text Box
            sqlStat2.append("SELECT DISTINCT E.SUBMISSION_ID FROM FORM_DATA_ELEMENT E, FORM_DATA D ");
          sqlStat2.append("WHERE D.DATA_ELEMENT_ID = E.ID ");
          String searchField = maintFormSubmissionForm.getBasicSearchField();
          String searchKeyword = this.getFormattedKeyword(maintFormSubmissionForm.getBasicSearchKeyword(), maintFormSubmissionForm.getBasicSearchType());
          sqlStat2.append("AND D.DATA_STRING " + maintFormSubmissionForm.getBasicSearchType() + " ? ");
          if(!Utility.isEmpty(basicSearchKeyword) && !Utility.isEmpty(searchField)){
            sqlStat2.append("AND E.ELEMENT_ID = ? ");
          }         
          preStat2 = dbConn.prepareStatement(sqlStat2.toString());
          this.setPrepareStatement(preStat2, 1, searchKeyword);  
          if(!Utility.isEmpty(basicSearchKeyword) && !Utility.isEmpty(searchField)){
            this.setPrepareStatement(preStat2, 2, searchField);
          }
          rs2= preStat2.executeQuery();
          String tmpSubmissionID = ""; 
          while (rs2.next()) {
            tmpSubmissionID += ((tmpSubmissionID.length()>0)?",":"") + getResultSetInteger(rs2, "SUBMISSION_ID");
          }
          // For Select Box, Radio Button, Check Box
          sqlStat2 = new StringBuffer();
          sqlStat2.append("SELECT DISTINCT E.SUBMISSION_ID ");
          sqlStat2.append("FROM FORM_DATA_ELEMENT E, FORM_DATA D, FORM_SELECTION_FIELD F, FORM_SELECTION_FIELD_OPTION G ");
          sqlStat2.append("WHERE D.DATA_ELEMENT_ID = E.ID AND G.SELECTION_FIELD_ID = F.ID AND E.ELEMENT_ID = F.ELEMENT_ID AND D.DATA_STRING = G.START_VALUE ");
          sqlStat2.append("AND G.LABEL " + maintFormSubmissionForm.getBasicSearchType() + " ? ");
          if(!Utility.isEmpty(basicSearchKeyword) && !Utility.isEmpty(searchField)){
            sqlStat2.append("AND E.ELEMENT_ID = ? ");
          }
          preStat2 = dbConn.prepareStatement(sqlStat2.toString());
          this.setPrepareStatement(preStat2, 1, searchKeyword);  
          if(!Utility.isEmpty(basicSearchKeyword) && !Utility.isEmpty(searchField)){
            this.setPrepareStatement(preStat2, 2, searchField);
          }
          rs2= preStat2.executeQuery();
          while (rs2.next()) {
            tmpSubmissionID += ((tmpSubmissionID.length()>0)?",":"") + getResultSetInteger(rs2, "SUBMISSION_ID");
          }

          
          if (!Utility.isEmpty(tmpSubmissionID)) {
            sqlStat1.append("           AND E.ID IN ("+tmpSubmissionID+") ");
          }else{
            throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
          }
          //}
          try { rs2.close(); } catch (Exception ignore) {} finally { rs2 = null; }
          try { preStat2.close(); } catch (Exception ignore) {} finally { preStat2 = null; }
        }

        preStat1 = dbConn.prepareStatement(sqlStat1.toString());

        rs1= preStat1.executeQuery();
        while (rs1.next()) {
          submissionID += ((submissionID.length()>0)?",":"") + getResultSetInteger(rs1, "ID");
        }

        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID,  C.DATA_STRING, D.FIELD_NAME, D.ELEMENT_TYPE, D.REQUIRED_FLAG, F.LABEL, A.SEQ, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, D.VALIDATION_RULE ");
        sqlStat.append("FROM FORM_ELEMENT D, MTM_FORM_PAGE_FORM_SECTION MP, MTM_FORM_SECTION_FORM_ELEMENT MS ");
        sqlStat.append("LEFT JOIN FORM_DATA_ELEMENT A ON MS.ELEMENT_ID = A.ELEMENT_ID ");
        sqlStat.append("RIGHT JOIN FORM_SUBMISSION B ON A.SUBMISSION_ID = B.ID ");
        sqlStat.append("LEFT JOIN FORM_DATA C ON A.ID = C.DATA_ELEMENT_ID ");
        sqlStat.append("LEFT JOIN FORM_SELECTION_FIELD E ON E.ELEMENT_ID = A.ELEMENT_ID ");
        sqlStat.append("LEFT JOIN FORM_SELECTION_FIELD_OPTION F ON E.ID = F.SELECTION_FIELD_ID AND C.DATA_STRING = F.START_VALUE ");
        sqlStat.append("WHERE  MP.SECTION_ID = MS.SECTION_ID AND D.ID = MS.ELEMENT_ID ");
        sqlStat.append("AND A.RECORD_STATUS = B.RECORD_STATUS AND C.RECORD_STATUS = D.RECORD_STATUS AND MP.RECORD_STATUS = MS.RECORD_STATUS  ");
        sqlStat.append("AND A.RECORD_STATUS = D.RECORD_STATUS AND A.RECORD_STATUS = MP.RECORD_STATUS ");
        sqlStat.append("AND MP.PAGE_ID = ? AND MS.SECTION_ID = ? ");
        if (!Utility.isEmpty(submissionID)) {
          sqlStat.append("           AND B.ID IN ("+submissionID+") ");
        }else{
          sqlStat.append("           AND B.ID IN (-1) ");
        }
        sqlStat.append("AND ((A.RECORD_STATUS = ? AND B.STATUS <> ? ) ");
        sqlStat.append("OR A.ID IS NULL) ");
        sqlStat.append(additiveQuery);
        sqlStat.append("ORDER BY MS.ELEMENT_SEQ , B.ID ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, pageID);
        this.setPrepareStatement(preStat, 2, sectionID);
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 4, FormSubmission.FORM_SUBMISSION_STATU_SAVED);

        rs = preStat.executeQuery();


        String elementType = null;
        String tmpStr1 = "";
        String tmpStr2 = "";
        Object[] tempObject = new Object[7];
        boolean isSecond = false;
        FormFileAttachmentDAObject formFileAttachDAO = new FormFileAttachmentDAObject(sessionContainer,dbConn);
        
        while (rs.next()) {          
          Object[] objFormElementFormData = new Object[7];
          String valueString = "";
          objFormElementFormData[0] = pageID.toString();
          objFormElementFormData[1] = sectionID.toString();
          objFormElementFormData[2] = (!Utility.isEmpty(getResultSetInteger(rs, "ELEMENT_ID"))) ? getResultSetInteger(rs, "ELEMENT_ID").toString(): "";
          objFormElementFormData[3] = (!Utility.isEmpty(getResultSetInteger(rs, "SUBMISSION_ID"))) ? getResultSetInteger(rs, "SUBMISSION_ID").toString(): "";
          elementType = getResultSetString(rs, "ELEMENT_TYPE");
          objFormElementFormData[4] = (!Utility.isEmpty(getResultSetString(rs, "FIELD_NAME"))) ? getResultSetString(rs, "FIELD_NAME"): "";
          
          objFormElementFormData[6] = "";
          if (!Utility.isEmpty(getResultSetString(rs, "VALIDATION_RULE")) && FormElement.VALIDATION_INTEGER_ONLY.endsWith(getResultSetString(rs, "VALIDATION_RULE")) ){
            objFormElementFormData[6] = FormElement.VALIDATION_INTEGER_ONLY;
          }else if (!Utility.isEmpty(getResultSetString(rs, "VALIDATION_RULE")) && FormElement.VALIDATION_NUMERIC_ONLY.endsWith(getResultSetString(rs, "VALIDATION_RULE")) ){
            objFormElementFormData[6] = FormElement.VALIDATION_NUMERIC_ONLY;
          }
          
          if(FormElementType.isSelection(getResultSetString(rs, "ELEMENT_TYPE"))){
            objFormElementFormData[5] = getResultSetString(rs, "LABEL");

            if(FormElementType.ELEMENT_CHECK_BOX.equals(elementType) ){

              if(!isSecond){
                tempObject[0] = objFormElementFormData[0];
                tempObject[1] = objFormElementFormData[1];
                tempObject[2] = objFormElementFormData[2];
                tempObject[3] = objFormElementFormData[3];
                tempObject[4] = objFormElementFormData[4];
                tempObject[5] = objFormElementFormData[5];
                tempObject[6] = objFormElementFormData[6];
                isSecond = true;
              } else if (tempObject[0].equals(objFormElementFormData[0]) &&
                    tempObject[1].equals(objFormElementFormData[1]) &&
                    tempObject[2].equals(objFormElementFormData[2]) &&
                    tempObject[3].equals(objFormElementFormData[3]) &&
                    tempObject[4].equals(objFormElementFormData[4]) ){
                    if((!Utility.isEmpty(tempObject[5]))){
                      tmpStr1 = tempObject[5].toString() + ";";
                    }
                    if((!Utility.isEmpty(objFormElementFormData[5]))){
                      tmpStr2 = objFormElementFormData[5].toString();
                    }
                    tempObject[5] = tmpStr1 +  tmpStr2 ;
              } else {
                result.add(tempObject);
                tempObject = new Object[7];
                tempObject[0] = objFormElementFormData[0];
                tempObject[1] = objFormElementFormData[1];
                tempObject[2] = objFormElementFormData[2];
                tempObject[3] = objFormElementFormData[3];
                tempObject[4] = objFormElementFormData[4];
                tempObject[5] = objFormElementFormData[5];
                tempObject[6] = objFormElementFormData[6];
                isSecond = true;
              }
            } else {
              if(!Utility.isEmpty(tempObject[0])){
                result.add(tempObject);
                tempObject = new Object[7];
              }
              result.add(objFormElementFormData);
            }
          }else if(FormElementType.ELEMENT_FILE.equals(elementType) ){
            List tmpAttachmentList = formFileAttachDAO.getFormFileAttachmentListByFormDataElementID(getResultSetInteger(rs, "ID"));
            if (!Utility.isEmpty(tmpAttachmentList)){
              for (int i=0; i<tmpAttachmentList.size(); i++) {
                FormFileAttachment attachment = (FormFileAttachment)tmpAttachmentList.get(i);  
                valueString += attachment.getAttachmentName() + ";";               
              }
            }
            if(!Utility.isEmpty(tempObject[0])){
              result.add(tempObject);
              tempObject = new Object[7];
            }
            objFormElementFormData[5] = valueString;            
            result.add(objFormElementFormData);
          }else{
            if(!Utility.isEmpty(tempObject[0])){
              result.add(tempObject);
              tempObject = new Object[7];
            }
            objFormElementFormData[5] = getResultSetString(rs, "DATA_STRING");            
            result.add(objFormElementFormData);
          }

        }
        if(!Utility.isEmpty(tempObject[0])){
          result.add(tempObject);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); rs1.close();} catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close();preStat1.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }


  public synchronized Integer getFormDataSeqIDBySectionIDElementIDSubmissionIDWFProgressID(
      Integer sectionID,
      Integer elementID,
      Integer submissionID,
      Integer workflowProgressID,
      Integer creatorID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatTmp = new StringBuffer();
    List result = new ArrayList();
        
    synchronized(dbConn) {
      try {
                
        sqlStat.append("SELECT A.SEQ, B.ID, B.DATA_ELEMENT_ID, B.DATA_STRING, B.DATA_NUMERIC, B.DATA_DATETIME, B.RECORD_STATUS, B.UPDATE_COUNT, B.CREATOR_ID, B.CREATE_DATE, B.UPDATER_ID, B.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A, FORM_DATA B ");
        sqlStat.append("WHERE A.ID = B.DATA_ELEMENT_ID ");
        sqlStat.append("AND A.SECTION_ID = ? AND A.ELEMENT_ID = ? AND A.SUBMISSION_ID =? ");
        sqlStat.append("AND A.CREATOR_ID = ? ");
        if (!Utility.isEmpty(workflowProgressID) && !"null".equals(workflowProgressID)){       
          sqlStat.append("AND A.WORKFLOW_PROGRESS_ID =? AND A.SUBMIT_FLAG = ?  ");
        }else{
          sqlStat.append("AND A.WORKFLOW_PROGRESS_ID is NULL ");
        }
        sqlStat.append("ORDER BY A.SEQ DESC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, sectionID);
        this.setPrepareStatement(preStat, 2, elementID);
        this.setPrepareStatement(preStat, 3, submissionID);
        this.setPrepareStatement(preStat, 4, creatorID);

        if (!Utility.isEmpty(workflowProgressID) && !"null".equals(workflowProgressID)){
          this.setPrepareStatement(preStat, 5, workflowProgressID);
          this.setPrepareStatement(preStat, 6, FormSubmission.FORM_SUBMISSION_STATU_SAVED);
        }

        rs = preStat.executeQuery();
        Integer seqNo = new Integer(-1);
        if (rs.next()){
          seqNo = getResultSetInteger(rs, "SEQ");
        }
        return(seqNo);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }


  public synchronized List getElementValueByPageIDSectionIDSubmissionIDElementIDSeqNo(
      Integer submissionID,
      Integer pageID,
      Integer sectionID,
      Integer elementID,
      Integer seqNo,     
      Integer creatorID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    synchronized(dbConn) {
      try {

        sqlStat.append("SELECT B.ID, B.DATA_ELEMENT_ID, B.DATA_STRING, B.DATA_NUMERIC, B.DATA_DATETIME, B.RECORD_STATUS, B.UPDATE_COUNT, B.CREATOR_ID, B.CREATE_DATE, B.UPDATER_ID, B.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A, FORM_DATA B ");
        sqlStat.append("WHERE A.ID = B.DATA_ELEMENT_ID ");
        sqlStat.append("AND A.SUBMISSION_ID =? ");
        sqlStat.append("AND A.PAGE_ID = ? ");
        sqlStat.append("AND A.SECTION_ID = ? ");
        sqlStat.append("AND A.ELEMENT_ID = ? ");
        sqlStat.append("AND A.SEQ = ? ");
        sqlStat.append("AND A.CREATOR_ID = ? ");
        
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, submissionID);
        this.setPrepareStatement(preStat, 2, pageID);
        this.setPrepareStatement(preStat, 3, sectionID);
        this.setPrepareStatement(preStat, 4, elementID);
        this.setPrepareStatement(preStat, 5, seqNo);
        this.setPrepareStatement(preStat, 6, creatorID);
       
        rs = preStat.executeQuery();

        while (rs.next()) {
          FormData tmpFormData = new FormData();
          tmpFormData.setID(getResultSetInteger(rs, "ID"));
          tmpFormData.setDataElementID(getResultSetInteger(rs, "DATA_ELEMENT_ID"));
          tmpFormData.setDataString(getResultSetString(rs, "DATA_STRING"));
          tmpFormData.setDataNumeric(getResultSetFloat(rs, "DATA_NUMERIC"));
          tmpFormData.setDataDatetime(getResultSetTimestamp(rs, "DATA_DATETIME"));
          tmpFormData.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormData.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormData.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormData.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormData.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormData.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormData.setCreatorName(UserInfoFactory.getUserFullName(tmpFormData.getCreatorID()));
          tmpFormData.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormData.getUpdaterID()));
          result.add(tmpFormData);

        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * get element value list by submission ID & element ID, and Form_Data.ID order by desc
   * @param submissionID
   * @param elementID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getElementValueListBySubmissionIDElementID(
      Integer submissionID,
      Integer elementID )
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    synchronized(dbConn) {
      try {

        sqlStat.append("SELECT B.ID, B.DATA_ELEMENT_ID, B.DATA_STRING, B.DATA_NUMERIC, B.DATA_DATETIME, B.RECORD_STATUS, B.UPDATE_COUNT, B.CREATOR_ID, B.CREATE_DATE, B.UPDATER_ID, B.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A, FORM_DATA B ");
        sqlStat.append("WHERE A.ID = B.DATA_ELEMENT_ID ");
        sqlStat.append("AND   A.SUBMISSION_ID = ? ");
        sqlStat.append("AND   A.ELEMENT_ID = ? ");
        sqlStat.append("AND   A.RECORD_STATUS = ? ");
        sqlStat.append("AND   A.RECORD_STATUS = B.RECORD_STATUS ");
        sqlStat.append("ORDER BY B.ID DESC ");        

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, submissionID);
        this.setPrepareStatement(preStat, 2, elementID);
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);

        rs = preStat.executeQuery();

        while (rs.next()) {
          FormData tmpFormData = new FormData();
          tmpFormData.setID(getResultSetInteger(rs, "ID"));
          tmpFormData.setDataElementID(getResultSetInteger(rs, "DATA_ELEMENT_ID"));
          tmpFormData.setDataString(getResultSetString(rs, "DATA_STRING"));
          tmpFormData.setDataNumeric(getResultSetFloat(rs, "DATA_NUMERIC"));
          tmpFormData.setDataDatetime(getResultSetTimestamp(rs, "DATA_DATETIME"));
          tmpFormData.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormData.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormData.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormData.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormData.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormData.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormData.setCreatorName(UserInfoFactory.getUserFullName(tmpFormData.getCreatorID()));
          tmpFormData.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormData.getUpdaterID()));
          result.add(tmpFormData);

        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get for data list to Excel for advance search
   * @param pageID
   * @param sectionID
   * @param searchForm
   * @return
   * @throws ApplicationException
   */
  public synchronized List getFormElementFormDataListByPageIDSectionIDAdvanceSearch(
      Integer pageID,
      Integer sectionID,
      AbstractSearchForm searchForm,
      boolean isTracking ) throws ApplicationException {
    String formRecordID = ((ListFormSubmissionForm)searchForm).getFormRecordID();
    PreparedStatement preStat = null;
    PreparedStatement preStat2 = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rs2 = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    StringBuffer attachedSqlStat = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    ListFormSubmissionForm listFormSubmissionForm = (ListFormSubmissionForm)searchForm;
    String fromDate = listFormSubmissionForm.getFromDate();
    String toDate = TextUtility.dbDateAdder(listFormSubmissionForm.getToDate(), 1);
    String searchSubmitter = listFormSubmissionForm.getSearchSubmitter();
    String attachedString = "";
    String attachedString2 = "";

    Map searchPanelMap = new HashMap();
    Object[] textBoxObject = new Object[3];
    Object[] datePickerObject = new Object[3];
    Object[] selectionObject = new Object[2];
    searchPanelMap = listFormSubmissionForm.getSearchPanelMap();
    textBoxObject = (Object[])searchPanelMap.get("textBoxObject");
    selectionObject = (Object[])searchPanelMap.get("selectionObject");
    datePickerObject = (Object[])searchPanelMap.get("datePickerObject");
        
    StringBuffer sqlStat2 = new StringBuffer();

    if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
        if (!Utility.isEmpty(fromDate) && Utility.isEmpty(toDate)) {
        attachedString = " AND C.UPDATE_DATE >= TO_DATE('" + fromDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
      } else if (Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        attachedString = " AND C.UPDATE_DATE <= TO_DATE('" + toDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
      } else if (!Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        attachedString = " AND C.UPDATE_DATE BETWEEN TO_DATE('" + fromDate + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('"+  toDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
      }
    } else {
        if (!Utility.isEmpty(fromDate) && Utility.isEmpty(toDate)) {
        attachedString = " AND C.UPDATE_DATE >= '" + fromDate + "' ";
      } else if (Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        attachedString = " AND C.UPDATE_DATE <= '" + toDate + "' ";
      } else if (!Utility.isEmpty(fromDate) && !Utility.isEmpty(toDate)) {
        attachedString = " AND C.UPDATE_DATE BETWEEN '" + fromDate + "' AND '"+  toDate + "' ";
      }
    }  
    
    
    if (!Utility.isEmpty(datePickerObject)){  
      Object[] elementIDArr = (Object[])datePickerObject[0];
      Object[] fromDateArr = (Object[])datePickerObject[1];
      Object[] toDateArr = (Object[])datePickerObject[2];
      for (int i=0; i<elementIDArr.length; i++){               
        if (!Utility.isEmpty(elementIDArr[i].toString()) ){
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if (!Utility.isEmpty(fromDateArr[i].toString()) && Utility.isEmpty(toDateArr[i].toString())) {
              attachedString2 = " AND D.DATA_STRING >= TO_DATE('" + fromDateArr[i] + "', 'YYYY-MM-DD HH24:MI:SS') AND E.ELEMENT_ID = " + elementIDArr[i] + " ";
            } else if (Utility.isEmpty(fromDateArr[i].toString()) && !Utility.isEmpty(toDateArr[i].toString())) {
              attachedString2 = " AND D.DATA_STRING <= TO_DATE('" + TextUtility.dbDateAdder(toDateArr[i].toString(),1) + "', 'YYYY-MM-DD HH24:MI:SS') AND E.ELEMENT_ID = " + elementIDArr[i] + " ";
            } else if (!Utility.isEmpty(fromDateArr[i].toString()) && !Utility.isEmpty(toDateArr[i].toString())) {
              attachedString2 = " AND D.DATA_STRING BETWEEN TO_DATE('" + fromDateArr[i] + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('"+  TextUtility.dbDateAdder(toDateArr[i].toString(),1) + "', 'YYYY-MM-DD HH24:MI:SS') AND E.ELEMENT_ID = " + elementIDArr[i] + " ";
            }
          } else {
            if (!Utility.isEmpty(fromDateArr[i].toString()) && Utility.isEmpty(toDateArr[i].toString())) {
              attachedString2 = " AND D.DATA_STRING >= '" + fromDateArr[i] + "' AND E.ELEMENT_ID = " + elementIDArr[i] + " ";
            } else if (Utility.isEmpty(fromDateArr[i].toString()) && !Utility.isEmpty(toDateArr[i].toString())) {
              attachedString2 = " AND D.DATA_STRING <= '" + TextUtility.dbDateAdder(toDateArr[i].toString(),1) + "' AND E.ELEMENT_ID = " + elementIDArr[i] + " ";
            } else if (!Utility.isEmpty(fromDateArr[i].toString()) && !Utility.isEmpty(toDateArr[i].toString())) {
              attachedString2 = " AND D.DATA_STRING BETWEEN '" + fromDateArr[i] + "' AND '"+  TextUtility.dbDateAdder(toDateArr[i].toString(),1) + "' AND E.ELEMENT_ID = " + elementIDArr[i] + " ";
            }
          }
          if (!Utility.isEmpty(attachedString2)){
            sqlStat2.append("SELECT DISTINCT E.SUBMISSION_ID FROM FORM_DATA_ELEMENT E, FORM_DATA D ");
            sqlStat2.append("WHERE D.DATA_ELEMENT_ID = E.ID ");  
            sqlStat2.append(attachedString2);  
            try {
            preStat2 = dbConn.prepareStatement(sqlStat2.toString());
            rs2= preStat2.executeQuery();
            String submissionID = "";
            while (rs2.next()) {
              submissionID += ((submissionID.length()>0)?",":"") + getResultSetInteger(rs2, "SUBMISSION_ID");
            }    
            submissionID = !Utility.isEmpty(submissionID) ? submissionID : "-1";
            attachedSqlStat.append("AND A.SUBMISSION_ID in ("+submissionID+") "); 
            sqlStat2 = new StringBuffer();
            } catch (ApplicationException appEx) {
              throw appEx;
            } catch (SQLException sqle) {
              log.error(sqle, sqle);
              throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
            } catch (Exception e) {
              log.error(e, e);
              throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
            }
         }
        }
      }
      
    }

    synchronized(dbConn) {
      try {
        
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID,  C.DATA_STRING, D.FIELD_NAME, D.ELEMENT_TYPE, D.REQUIRED_FLAG, F.LABEL, A.SEQ, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, D.VALIDATION_RULE ");
        sqlStat.append("FROM FORM_ELEMENT D, MTM_FORM_PAGE_FORM_SECTION MP, MTM_FORM_SECTION_FORM_ELEMENT MS ");
        sqlStat.append("LEFT JOIN FORM_DATA_ELEMENT A ON MS.ELEMENT_ID = A.ELEMENT_ID ");
        sqlStat.append("RIGHT JOIN FORM_SUBMISSION B ON A.SUBMISSION_ID = B.ID ");
        sqlStat.append("LEFT JOIN FORM_DATA C ON A.ID = C.DATA_ELEMENT_ID ");
        sqlStat.append("LEFT JOIN FORM_SELECTION_FIELD E ON E.ELEMENT_ID = A.ELEMENT_ID ");
        sqlStat.append("LEFT JOIN FORM_SELECTION_FIELD_OPTION F ON E.ID = F.SELECTION_FIELD_ID AND C.DATA_STRING = F.START_VALUE ");
        sqlStat.append("WHERE  MP.SECTION_ID = MS.SECTION_ID AND D.ID = MS.ELEMENT_ID ");
        sqlStat.append("AND A.RECORD_STATUS = B.RECORD_STATUS AND C.RECORD_STATUS = D.RECORD_STATUS AND MP.RECORD_STATUS = MS.RECORD_STATUS  ");
        sqlStat.append("AND A.RECORD_STATUS = D.RECORD_STATUS AND A.RECORD_STATUS = MP.RECORD_STATUS ");
        sqlStat.append("AND MP.PAGE_ID = ? AND MS.SECTION_ID = ? ");
        sqlStat.append("AND A.RECORD_STATUS = ?  ");
        if (isTracking) {
          sqlStat.append("AND B.CREATOR_ID = '"+ sessionContainer.getUserRecordID() +"' ");
        }else{
          sqlStat.append("AND B.STATUS <> '" + FormSubmission.FORM_SUBMISSION_STATU_SAVED + "' ");
          if (!Utility.isEmpty(searchSubmitter) && !"null".equals(searchSubmitter)){
            sqlStat.append("AND B.CREATOR_ID = " + searchSubmitter + " ");
          }
        } 
         
        //
        
        Object[] elementIDArr = (Object[])textBoxObject[0];
        Object[] typeArr = (Object[])textBoxObject[1];
        Object[] keywordArr = (Object[])textBoxObject[2];
        for (int i=0; i<elementIDArr.length; i++){               
          if (!Utility.isEmpty(elementIDArr[i].toString()) ){         
            if (!Utility.isEmpty(keywordArr[i].toString())){
              sqlStat2.append("SELECT DISTINCT E.SUBMISSION_ID FROM FORM_DATA_ELEMENT E, FORM_DATA D ");
              sqlStat2.append("WHERE D.DATA_ELEMENT_ID = E.ID ");             
              String searchKeyword = this.getFormattedKeyword(keywordArr[i].toString(), typeArr[i].toString());
              sqlStat2.append("AND D.DATA_STRING " + typeArr[i] + "  ? ");
              sqlStat2.append("AND E.ELEMENT_ID = " + elementIDArr[i] + " ");    
              preStat2 = dbConn.prepareStatement(sqlStat2.toString());
              this.setPrepareStatement(preStat2, 1, searchKeyword);
              rs2= preStat2.executeQuery();
              String submissionID = "";
              while (rs2.next()) {
                submissionID += ((submissionID.length()>0)?",":"") + getResultSetInteger(rs2, "SUBMISSION_ID");
              }    
              submissionID = !Utility.isEmpty(submissionID) ? submissionID : "-1";
              sqlStat.append("AND A.SUBMISSION_ID in ("+submissionID+") "); 
              sqlStat2 = new StringBuffer();
            } 
          }          
        }

        Object[] elementIDArr1 = (Object[])selectionObject[0];       
        Object[] keywordArr1 = (Object[])selectionObject[1];
        for (int i=0; i<elementIDArr1.length; i++){               
          if (!Utility.isEmpty(elementIDArr1[i].toString()) ){                
            if (!Utility.isEmpty(keywordArr1[i].toString())){
              sqlStat2.append("SELECT DISTINCT E.SUBMISSION_ID FROM FORM_DATA_ELEMENT E, FORM_DATA D ");
              sqlStat2.append("WHERE D.DATA_ELEMENT_ID = E.ID ");             
              sqlStat2.append("AND D.DATA_STRING = " +  keywordArr1[i] + " ");
              sqlStat2.append("AND E.ELEMENT_ID = " + elementIDArr1[i] + " "); 
              preStat2 = dbConn.prepareStatement(sqlStat2.toString());
              rs2= preStat2.executeQuery();
              String submissionID = "";
              while (rs2.next()) {
                submissionID += ((submissionID.length()>0)?",":"") + getResultSetInteger(rs2, "SUBMISSION_ID");
              }    
              submissionID = !Utility.isEmpty(submissionID) ? submissionID : "-1";
              sqlStat.append("AND A.SUBMISSION_ID in ("+submissionID+") "); 
              sqlStat2 = new StringBuffer();
            }
          }         
        } 
        //display format
        String notDisplayStr = listFormSubmissionForm.getDisplayFormat();
        String elementStr =  notDisplayStr.substring(0,notDisplayStr.indexOf("|"));
        String sectionStr =  notDisplayStr.substring(notDisplayStr.indexOf("|")+1,notDisplayStr.lastIndexOf("|"));
        String pageStr = notDisplayStr.substring(notDisplayStr.lastIndexOf("|")+1,notDisplayStr.length());
        if (!Utility.isEmpty(listFormSubmissionForm.getDisplayFormat()) && !"null".equals(listFormSubmissionForm.getDisplayFormat())){
          if (!Utility.isEmpty(elementStr)){
            sqlStat.append("AND A.ELEMENT_ID NOT IN ( " + elementStr + " ) ");
          }
          if (!Utility.isEmpty(sectionStr)){
            sqlStat.append("AND A.SECTION_ID NOT IN ( " + sectionStr + " ) ");
          }
          if (!Utility.isEmpty(pageStr)){
            sqlStat.append("AND A.PAGE_ID NOT IN ( " + pageStr + " ) ");
          }
        }
        //add form date picker search.
        sqlStat.append(attachedSqlStat);
        sqlStat.append(attachedString);   
        
        //Form the ORDER clause for sorting.
        sqlStat.append("ORDER BY MS.ELEMENT_SEQ , B.ID ");
        sqlStat = this.getFormattedSQL(sqlStat.toString());
        
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, pageID);
        this.setPrepareStatement(preStat, 2, sectionID);
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);

        rs = preStat.executeQuery();


        String elementType = null;
        String tmpStr1 = "";
        String tmpStr2 = "";
        Object[] tempObject = new Object[7];
        boolean isSecond = false;
        
        FormFileAttachmentDAObject formFileAttachDAO = new FormFileAttachmentDAObject(sessionContainer,dbConn);

        while (rs.next()) {          
          Object[] objFormElementFormData = new Object[7];
          String valueString = "";
          objFormElementFormData[0] = pageID.toString();
          objFormElementFormData[1] = sectionID.toString();
          objFormElementFormData[2] = (!Utility.isEmpty(getResultSetInteger(rs, "ELEMENT_ID"))) ? getResultSetInteger(rs, "ELEMENT_ID").toString(): "";
          objFormElementFormData[3] = (!Utility.isEmpty(getResultSetInteger(rs, "SUBMISSION_ID"))) ? getResultSetInteger(rs, "SUBMISSION_ID").toString(): "";
          elementType = getResultSetString(rs, "ELEMENT_TYPE");
          objFormElementFormData[4] = (!Utility.isEmpty(getResultSetString(rs, "FIELD_NAME"))) ? getResultSetString(rs, "FIELD_NAME"): "";
          objFormElementFormData[6] = "";
          if (!Utility.isEmpty(getResultSetString(rs, "VALIDATION_RULE")) && FormElement.VALIDATION_INTEGER_ONLY.endsWith(getResultSetString(rs, "VALIDATION_RULE")) ){
            objFormElementFormData[6] = FormElement.VALIDATION_INTEGER_ONLY;
          }else if (!Utility.isEmpty(getResultSetString(rs, "VALIDATION_RULE")) && FormElement.VALIDATION_NUMERIC_ONLY.endsWith(getResultSetString(rs, "VALIDATION_RULE")) ){
            objFormElementFormData[6] = FormElement.VALIDATION_NUMERIC_ONLY;
          }
          
          if(FormElementType.isSelection(getResultSetString(rs, "ELEMENT_TYPE"))){
            objFormElementFormData[5] = getResultSetString(rs, "LABEL");

            if(FormElementType.ELEMENT_CHECK_BOX.equals(elementType) ){

              if(!isSecond){
                tempObject[0] = objFormElementFormData[0];
                tempObject[1] = objFormElementFormData[1];
                tempObject[2] = objFormElementFormData[2];
                tempObject[3] = objFormElementFormData[3];
                tempObject[4] = objFormElementFormData[4];
                tempObject[5] = objFormElementFormData[5];
                tempObject[6] = objFormElementFormData[6];
                isSecond = true;
              } else if (tempObject[0].equals(objFormElementFormData[0]) &&
                    tempObject[1].equals(objFormElementFormData[1]) &&
                    tempObject[2].equals(objFormElementFormData[2]) &&
                    tempObject[3].equals(objFormElementFormData[3]) &&
                    tempObject[4].equals(objFormElementFormData[4]) ){
                    if((!Utility.isEmpty(tempObject[5]))){
                      tmpStr1 = tempObject[5].toString() + ";";
                    }
                    if((!Utility.isEmpty(objFormElementFormData[5]))){
                      tmpStr2 = objFormElementFormData[5].toString();
                    }
                    tempObject[5] = tmpStr1 +  tmpStr2 ;
              } else {
                result.add(tempObject);
                tempObject = new Object[7];
                tempObject[0] = objFormElementFormData[0];
                tempObject[1] = objFormElementFormData[1];
                tempObject[2] = objFormElementFormData[2];
                tempObject[3] = objFormElementFormData[3];
                tempObject[4] = objFormElementFormData[4];
                tempObject[5] = objFormElementFormData[5];
                tempObject[6] = objFormElementFormData[6];
                isSecond = true;
              }
            } else {
              if(!Utility.isEmpty(tempObject[0])){
                result.add(tempObject);
                tempObject = new Object[7];
              }
              result.add(objFormElementFormData);
            }
          }else if(FormElementType.ELEMENT_FILE.equals(elementType) ){
            List tmpAttachmentList = formFileAttachDAO.getFormFileAttachmentListByFormDataElementID(getResultSetInteger(rs, "ID"));
            if (!Utility.isEmpty(tmpAttachmentList)){
              for (int i=0; i<tmpAttachmentList.size(); i++) {
                FormFileAttachment attachment = (FormFileAttachment)tmpAttachmentList.get(i);  
                valueString += attachment.getAttachmentName() + ";";               
              }
            }
            if(!Utility.isEmpty(tempObject[0])){
              result.add(tempObject);
              tempObject = new Object[7];
            }
            objFormElementFormData[5] = valueString;            
            result.add(objFormElementFormData);
          }else{
            if(!Utility.isEmpty(tempObject[0])){
              result.add(tempObject);
              tempObject = new Object[7];
            }
            objFormElementFormData[5] = getResultSetString(rs, "DATA_STRING");            
            result.add(objFormElementFormData);
          }

        }
        if(!Utility.isEmpty(tempObject[0])){
          result.add(tempObject);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); rs2.close();} catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close();preStat2.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get progress ID
   * @param seq
   * @return
   * @throws ApplicationException
   */
  public synchronized AbstractBaseObject getFormDataElementBySeq(Integer seq)throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    Integer result = null;
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE  A.SEQ = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, seq);
        rs = preStat.executeQuery();
        if (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();
          tmpFormDataElement.setID(getResultSetInteger(rs, "ID"));
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          tmpFormDataElement.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormDataElement.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormDataElement.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormDataElement.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormDataElement.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormDataElement.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormDataElement.setCreatorName(UserInfoFactory.getUserFullName(tmpFormDataElement.getCreatorID()));
          tmpFormDataElement.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormDataElement.getUpdaterID()));
          return(tmpFormDataElement);
        } else {
          return null;
        }
               
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get form data element list by seq, but only a value by the element. 
   * @param seq
   * @return
   * @throws ApplicationException
   */
  public synchronized List getFormDataElementListBySeq(Integer seq)throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.PAGE_ID, A.SECTION_ID, A.SUBMISSION_ID, A.ELEMENT_ID, A.WORKFLOW_PROGRESS_ID, A.SEQ, A.SUBMIT_FLAG, A.RECORD_STATUS, MS.ELEMENT_SEQ  ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A, MTM_FORM_SECTION_FORM_ELEMENT MS ");
        sqlStat.append("WHERE  A.ELEMENT_ID = MS.ELEMENT_ID AND A.SEQ = ? ");
        sqlStat.append("ORDER BY  MS.ELEMENT_SEQ ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, seq);
        rs = preStat.executeQuery();
        while (rs.next()) {
          FormDataElement tmpFormDataElement = new FormDataElement();         
          tmpFormDataElement.setPageID(getResultSetInteger(rs, "PAGE_ID"));
          tmpFormDataElement.setSectionID(getResultSetInteger(rs, "SECTION_ID"));
          tmpFormDataElement.setSubmissionID(getResultSetInteger(rs, "SUBMISSION_ID"));
          tmpFormDataElement.setElementID(getResultSetInteger(rs, "ELEMENT_ID"));
          tmpFormDataElement.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpFormDataElement.setSeq(getResultSetInteger(rs, "SEQ"));
          tmpFormDataElement.setSubmitFlag(getResultSetString(rs, "SUBMIT_FLAG"));
          
          result.add(tmpFormDataElement);
        }
        
        return (result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get max seq in a step by userRecordID
   * @param progressID
   * @param userRecordID
   * @return
   * @throws ApplicationException
   */
  public synchronized int getFormDataSeqByProgressIDUserID(
      Integer progressID,
      Integer userRecordID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    int result = 0;
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT MAX(A.SEQ) ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE ");
        sqlStat.append("A.WORKFLOW_PROGRESS_ID = ? AND A.CREATOR_ID = ?  ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, progressID);
        this.setPrepareStatement(preStat, 2, userRecordID);
        
        rs = preStat.executeQuery();

        if (rs.next()) {
          result = rs.getInt(1);
        }       
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get Previous seq by seq.  
   * @param submissionID
   * @param elementID
   * @param seq
   * @return
   * @throws ApplicationException
   */
  public synchronized int getPreviousElementSeqBySubmissionIDElementIDSeq(    
      Integer submissionID,
      Integer elementID,
      Integer seq)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    int result = 0;
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT MAX(A.SEQ) ");
        sqlStat.append("FROM   FORM_DATA_ELEMENT A ");
        sqlStat.append("WHERE A.SUBMIT_FLAG <> 'D' ");
        sqlStat.append("AND A.SUBMISSION_ID =? AND A.ELEMENT_ID = ? AND A.SEQ < ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, submissionID);
        this.setPrepareStatement(preStat, 2, elementID);
        this.setPrepareStatement(preStat, 3, seq);
        rs = preStat.executeQuery();

        if (rs.next()) {
          result = rs.getInt(1);
        }       
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized void UpdateProgressIDBySubmissionID(Integer submissionID,Integer progressID) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("UPDATE FORM_DATA_ELEMENT  ");
        sqlStat.append("SET  WORKFLOW_PROGRESS_ID = ? ");
        sqlStat.append("WHERE  RECORD_STATUS = ? ");
        sqlStat.append("AND SUBMISSION_ID = ? ");
        // If this record is bind with workflow progress, either "S" or "D" status,
        // we should record down the workflow progress id for this record.
        // sqlStat.append("AND SUBMIT_FLAG = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, progressID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, submissionID);
        // this.setPrepareStatement(preStat, 4, FormSubmission.FORM_SUBMISSION_STATU_SAVED);
        preStat.executeUpdate();
               
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
}

